<?php

require_once('config.php');
include('header.php');
print '<script type="text/javascript" src="fighterdetails.js"></script>';
print '</head><body>';

$con = mysql_connect($dbhost,$dbuser,$dbpassword);
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db($dbname, $con);

function input_text($element_name, $values) {
     print '<input type="text" name="' . $element_name .'" value="';
     print htmlentities($values[$element_name]) . '">';
}

function input_submit($element_name, $label) {
     print '<input type="submit" name="' . $element_name .'" value="';
     print htmlentities($label) .'"/>';
}

function show_form() {
	print '<form method="POST" action="' . $_SERVER['PHP_SELF'] . '">';
	print 'Last Name: '; input_text('lastname', $_POST);
	print '<br />SCA Name: '; input_text('scaname', $_POST);
	print '<br />Location: <select name="area">';
	$result = mysql_query("SELECT id, name FROM locations");
	while($row = mysql_fetch_array($result)) {
	  print '<option value="'.$row['id'].'">'.$row['name'].'</option>';
  	}
  	print '</select>';

	print '<input type="hidden" name="_submit_check" value="1"/><br />';
	input_submit('submit', 'Find');
	print '<br/><a href="excelexport.php">Export to Excel</a> (Takes a while - ONLY CLICK ONCE)';
}

show_form();

if (array_key_exists('_submit_check', $_POST)) {

$sql = 'SELECT p.id , p.society_name, l.name as location, '
        . ' count(distinct f.id) as authcount, count(distinct w.id) as warrantcount'
        . ' FROM people as p '
        . ' left join fighter_authorizations as f on p.id=f.person_id and f.suspended=0 '
        . ' left join marshal_warrants as w on p.id=w.person_id AND w.suspended=0'
        . ' left join locations as l on p.location_id = l.id'
        . ' WHERE p.society_name <> "" ';
    if (!empty($_POST["lastname"])) {
    	$sql = $sql . ' and p.last_name like "' . $_POST["lastname"] . '%"';
    }
    if (!empty($_POST["scaname"])) {
	    	$sql = $sql . ' and p.society_name like "' . $_POST["scaname"] . '%"';
    }
    if (!empty($_POST["area"]) && (intval($_POST["area"]) > 1)) {
	    	$sql = $sql . ' and p.location_id = ' . $_POST["area"];
    }
    $sql = $sql . ' group by 1, 2';
    $result = mysql_query($sql);

    print '<br /><table border=1>';
    print '<tr class="header"><td>SCA Name</td><td>Location</td><td>Authorizations</td><td>Warrants/Offices</td></tr>';
    while($row = mysql_fetch_array($result)) {
	  print '<div id="a'.$row['id'].'" class="tip"></div>';
	  print '<div id="w'.$row['id'].'" class="tip"></div>';
      print '<tr>';
      print '<td>'.$row['society_name'].'</td>';
      print '<td>'.$row['location'].'</td>';
      if (intval($row['authcount']) > 0) {
	      print '<td> <a href="#" onmouseout="showAuth(event,'.$row['id'].')" onmouseover="showAuth(event,'.$row['id'].')"  onclick="return false"> ';
	      print $row['authcount'].'</a></td>';
	  } else {
	      print '<td>0</td>';
	  }
      if (intval($row['warrantcount']) > 0) {
	      print '<td> <a href="#" onmouseout="showWarrant(event,'.$row['id'].')" onmouseover="showWarrant(event,'.$row['id'].')"  onclick="return false"> ';
	      print $row['warrantcount'].'</a></td>';
	  } else {
	      print '<td>0</td>';
	  }
      print '</tr>';
    }

    print '</table>';

}

include('footer.php');
?>